﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using QLTH_DTO;


namespace QLTH_DATASSECC
{
  public class clsQLNguoiDungDA:ConnectionData.clsKetNoi
    {
      String _sql;
      public DataTable DanhSachND()
      {
          _sql = "SELECT ND.MAGV,PQ.TENQUYEN,(GV.HOLOTGV +' '+GV.TENGV) AS HOTEN, TENDANGNHAP,MATKHAU,cast (TRANGTHAISD as bit) AS TRANGTHAI FROM QLNGUOIDUNG ND INNER JOIN PHANQUYEN PQ ON ND.MAPQ = PQ.MAPQ INNER JOIN GIAOVIEN GV ON ND.MAGV = GV.MAGV";
          return Load_DataTable(_sql);
      }

    
      /// <summary>
      /// Thêm người dùng
      /// </summary>
      /// <param name="nd"></param>
      /// <returns></returns>
      public int Them_nguoidung(clsQLNguoiDungDTO nd)
      {
          int namepara = 5;
          String[] name = new String[namepara];
          Object[] values = new Object[namepara];
          name[0] = "@Magv"; values[0] = nd.Gv.Magv;
          name[1] = "@Mapq"; values[1] = nd.Pq.Mapq;
          name[2] = "@Tendangnhap"; values[2] = nd.Tendn;
          name[3] = "@Matkhau"; values[3] = nd.Matkhau;
          name[4] = "@Trangthai"; values[4] = nd.Trangthai;
          _sql = "INSERT INTO QLNGUOIDUNG(MAGV,MAPQ,TENDANGNHAP,MATKHAU,TRANGTHAISD) VALUES(@Magv,@Mapq,@Tendangnhap,@Matkhau,@Trangthai)";
          return Nonquery(_sql, name, values, namepara);
      }

      public int Capnhat_nguoidung(clsQLNguoiDungDTO nd)
      {
          int namepara = 5;
          String[] name = new String[namepara];
          Object[] values = new Object[namepara];
          name[0] = "@Magv"; values[0] = nd.Gv.Magv;
          name[1] = "@Mapq"; values[1] = nd.Pq.Mapq;
          name[2] = "@TenDN"; values[2] = nd.Tendn;
          name[3] = "@Matkhau"; values[3] = nd.Matkhau;
          name[4] = "@Trangthai"; values[4] = nd.Trangthai;
          _sql = "UPDATE QLNGUOIDUNG SET MAPQ = @Mapq, TENDANGNHAP = @TenDN,MATKHAU = @Matkhau, TRANGTHAISD = @Trangthai WHERE MAGV = @Mgv";
          return Nonquery(_sql, name, values, namepara);
      }

      /// <summary>
      /// Lấy thông tinh giáo viên
      /// </summary>
      /// <param name="_user">tên user truyền vào</param>
      /// <returns></returns>
      public clsQLNguoiDungDTO ThongTinND(String _user)
      {
          String _sql = "SELECT ND.MAGV,GV.HOLOTGV, GV.TENGV,GV.NGAYSINH,GV.NOISINH,GV.DIACHI,GV.SDT,GV.PHAI,TCM.TOCHUYENMON ,HV.TENHOCVI,DT.TENDANTOC ,TG.TENTONGIAO,TENDANGNHAP,MATKHAU,TRANGTHAISD,PQ.TENQUYEN"
                            +" FROM QLNGUOIDUNG ND INNER JOIN GIAOVIEN GV ON ND.MAGV = GV.MAGV INNER JOIN PHANQUYEN PQ ON ND.MAPQ = PQ.MAPQ "
                          +"INNER JOIN TOCHUYENMON TCM ON GV.MATCM = TCM.MATCM INNER JOIN DANTOC DT ON GV.MADT= DT.MADT INNER JOIN TONGIAO TG ON GV.MATG = TG.MATG INNER JOIN HOCVI HV ON GV.MAHV = HV.MAHV  "
                          +" WHERE TENDANGNHAP = '"+_user+"'";
          clsQLNguoiDungDTO nd = null;
          OpenData(false);
          SqlDataReader dr = ExReader(_sql);
          while (dr.Read()) 
          {
              nd = new clsQLNguoiDungDTO();
              nd.Gv.Magv = Convert.ToString(dr["MAGV"]);
              nd.Gv.Holot = Convert.ToString(dr["HOLOTGV"]);
              nd.Gv.Ten = Convert.ToString(dr["TENGV"]);
              nd.Matkhau = Convert.ToString(dr["MATKHAU"]);
              nd.Trangthai = Convert.ToInt16(dr["TRANGTHAISD"]);
              nd.Pq.Tenpq = Convert.ToString(dr["TENQUYEN"]);
              try
              {                 
                  nd.Gv.Ngaysinh = Convert.ToDateTime(dr["NGAYSINH"]);
                  nd.Gv.Noisinh = Convert.ToString(dr["NOISINH"]);
                  nd.Gv.Phai = Convert.ToInt16(dr["PHAI"]);
                  nd.Gv.Diachi = Convert.ToString(dr["DIACHI"]);
                  nd.Gv.Sdt = Convert.ToInt16(dr["SDT"]);
                  nd.Gv.Tcm.Tentcm = Convert.ToString(dr["TOCHUYENMON"]);
                  nd.Gv.tongiao.Tentg = Convert.ToString(dr["TENTONGIAO"]);
                  nd.Gv.Hocvi.Tenhv = Convert.ToString(dr["TENHOCVI"]);
                  nd.Gv.dantoc.Tendt = Convert.ToString(dr["TENDANTOC"]);
                  nd.Tendn = Convert.ToString(dr["TENDANGNHAP"]);
                 
              }
              catch { }
          }
          CloseData(false);
          return nd;
      }


      public bool Doimatkhau(String _user, String _newpas)
      {
          int namepara = 1;
          String[] name = new String[namepara];
          Object[] values = new Object[namepara];
          name[0] = "@Matkhau"; values[0] = _newpas;
          _sql = "UPDATE QLNGUOIDUNG SET MATKHAU = '"+_newpas+"' WHERE TENDANGNHAP = '"+_user+"'";
          return Nonquery(_sql, name, values, namepara) > 0;
          
      }
    }
}
